Integrantes:

Introdução

Este trabalho trata da Avaliação 2 da disciplina de Banco de Dados do curso Ciêncisa de Dados/FGV-EMAp objetivando aperfeiçoar e colocar em prática os aprendizados do semestre. Neste caso, a proposta é capturar dados GeoJson, armanezar no banco MySql e, posteriormente, analisar as informações.

Para tanto, capturamos os dados de Arquivos GeoJSON do Estado de São Paulo e datasets com informações adicionais sobre os municípios, como o Índice de Nível Socioeconômico, o Índice de Desenvolvimento da Educação do Estado de São Paulo, fluxo escolar, a quantidade de população por município e referências sobre a segurança, extraídos do site Base dos Dados, afim de entedermos como essas estão distribuídas no espaço geográfico do Estado, averiguando quais municípios se destacam e como os dados se correlacionam.

Importando as bibliotecas necessárias

Importando e limpando os dados

Para esse trabalho pretendemos utilizar 5 diferentes tabelas, uma para os dados geométricos, do Geodata BR, e outras quatro com dados a respeito da educação, segurança e população do estado de São Paulo, da Base dos dados. Dessa forma, vamos primeiramente importar os dados dos arquivos GeoJSON e CSV para o pandas e o geopandas para limpá-los e posteriormente colocá-los no MySQL utilizando o pymysql e o sqlalchemy.

Geometria

Começaremos pelos dados de geometria:

A primeira vista, as colunas name e description parecem ser iguais. Caso sejam, desejados armazenar apenas uma delas. Conferindo se as colunas nome e descrição são iguais:

Como vimos acima, as duas colunas são iguais, então vamos remover a coluna description:

Vamos verificar o nome de maior tamanho na coluna name para posteriormente criar a tabela no MySQL (esse processo também foi feito para as outras colunas, mas não exibiremos esse processo para todas as colunas para não ser excessivamente repetitivo)

Com isso, vamos fazer de forma análoga para as próximas tabelas

IDESP das Escolas

Nesse caso, diferentemente de como foi feito anteriormente, também precisamos remover valores faltantes na coluna com o id do município (código do IBGE - o id da tabela anterior). Abaixo vamos ler o arquivo CSV e remover os valores faltantes:

Nível Sócio Economico das Escolas

De forma análoga as tabelas anteriores:

Fluxo Escolar

Nesse caso, além do que já foi feito anteriormente, também precisamos remover valores inconsistentes pois há campos na tabela com proporções maiores que 100%. Fazemos isso abaixo:

Ocorrências Registrasdas em São Paulo

De forma análoga as tabelas anteriores:

População de São Paulo

Além do que já foi feito anteriormente, nesse caso desejamos manter apenas os dados referentes ao estado de São Paulo, já que nas tabelas anteriores todos os dados já eram de São Paulo, porém nessa os dados são do Brasil inteiro. Fazemos isso abaixo:

Criando o banco de dados e as tabelas no MySQL

Criando o modelo físico

Agora que já temos os dados limpos adequadamente, desejamos criar um banco de dados no MySQL para armazenar todos os dados que temos. Para isso, primeiramente criamos um modelo físico no Vertabelo para nos auxiliar:

Modelo físico do vertabelo

Vale lembar que os tipos de dados foram definidos de acordo como já vimos anteriormente quando limpamos eles, assim como verificamos também os casos que temos valores faltantes (sendo que muitos deles desejamos manter assim, sem remover essas observações)

Criando a conexão com o MySQL

Devemos criar uma conexão com o MySQL para conseguir criar o banco de dados e as tabelas utilizando python. Para isso, nesta etapa, utilizaremos o pymysql:

Criando o banco de dados

Como estamos trabalhando coletivamente e esse notebook deve funcionar para rodar as "células" em sequência e funcionar todas as etapas, devemos remover (apagar, deletar) a base de dados caso exista no computador do respectivo usuário e criar ela novamente:

Usando a base de dados criada

Agora que criamos a base de dados, devemos usá-la para poder prosseguir com a etapa de criar tabelas. Utilizamos o nosso cursor novamente e passamos a utilizar a base de dados criada com o comando do MySQL:

Criando as tabelas

Utilizando o modelo que foi criado no Vertabelo, agora vamos criar nossas bases de dados no MySQL. Abaixo, criaremos as respectivas tabelas para cada subtópico.

Geometria

Nível Sócio Econômico das Escolas

Fluxo Escolar

IDESP

Ocorrências Registradas em São Paulo

População de São Paulo

Populando as tabelas

Agora que já criamos as tabelas corretamente no MySQL, desejamos popular nossa base de dados. Para isso, utilizaremos o pymysql e o sqlalchemy, já que temos uma tabela com dados de geometria e as demais não possuem.

Geometria

Populamos a tabela geometria utilizando o pymysql (pois o geopandas oferece métodos para popular tabelas no ProgreSQL, mas não no MySQL):

Vale lembrar que acima convertemos os dados de geometria ao inserir no MySQL para que o tipo de dado seja aceito como polygon no MySQL.

IDESP

Para as próximas tabelas utilizaremos o sqlalchemy, então devemos criar uma conexão por ele:

Agora, podemos popular a tabela dos dados do IDESP, utilizando o método to_sql() que o pandas oferece:

Faremos de forma análoga para as próximas tabelas, nos próximos subtópicos abaixo

Nível Sócio Economico das Escolas

Fluxo Escolar

Ocorrências Registrasdas em São Paulo

População de São Paulo

Adicionando as Foreign Keys

Agora que já populamos as tabelas, podemos adicionar as foreign keys, conforme nosso modelo físico criado no Vertabelo. Isso não foi feito anteriormente para não haver problemas ao popular a base de dados, pois a ordem para inserior os dados seria mais importante, sendo assim adicionar as foreign keys após popular as tabelas é mais conveniente. Adicionando as foreign keys:

Importando os dados para o pandas e o geopandas

Agora que conseguimos popular o banco de dados no MySQL corretamente, desejamos importá-los novamente para o pandas e o geopandas para que possamos fazer as análises com os dados. Utilizaremos nomes de variáveis diferentes para reforçar que estamos utilizando os dados que foram populados no MySQL.

Abaixo importaremos os dados dos repectivos subtópicos.

Geometria

Como podemos ver acima, conseguimos importar nossos dados novamente e fazê-los serem reconhecidos como geometria pelo geopandas.

Nível Sócio Econômico

IDESP das Escolas

Fluxo Escolar

Ocorrências Registrasdas em São Paulo

População de São Paulo

Realizando as Análises e Visualizações

Como já importamos os dados devidamente, agora podemos fazer as análises e visualizações que desejamos com os dados que temos. Pretendemos fazer gráficos interativos, mas precisamos salva-los, então vamos configurar o chart studio para salvar esses gráficos:

Índice de Nível Socieconômico

Para esta análise, usou-se uma tabela que contém dados que caracterizam brevemente a escola e, principalmente, dados do indicador socioeconômico da escola, o INSE. O indicador varia de 0 a 10, sendo 10 a escola com o nível socioeconômico mais baixo e 0 a escola com nível mais alto. O INSE é calculado a partir dos questionários do SARESP. Ele considera as seguintes variáveis: grau de escolaridade dos pais, posse de bens de consumo duráveis na residência e renda familiar.

Inicialmente, realizou-se a leitura da base com Geopandas e uma filtragem nos dados, para trabalhar somente com o necessário. Posteriormente, ainda, foram unidas as bases de dados com informações espaciais e do INSE do munícipio.

Na primeira plotagem, abaixo, é possível visualizar os nível socioeconômico (média das escolas) por município.

Pelo mapa, é vísivel que alguns munícipios possuem níveis socioeconômicos (média das escolas) mais elevados que outros. O município com melhor índice de nível socioecônomico é águas de São Pedro (1.66), já com o pior INSE, é Itaóca (8.56), como verificado pelos gráficos de barras a seguir, que indicam os 10 melhores e 10 piores INSE.

Índice de Desenvolvimento da Educação do Estado de São Paulo

A tabela usada, nesse caso, contém dados da nota do Índice de Desenvolvimento da Educação do Estado de São Paulo (IDESP) para cada escola da rede estadual do estado de São Paulo, estabelecendo metas que as escolas devem alcançar ano a ano e seus resultados. Disponibilizada pela Secretaria de Educação do Estado de São Paulo, a tabela identifica cada escola através de códigos nacionais e estaduais e disponibiliza variáveis que demonstram para aquela dada escola, seu aproveitamento no IDESP. O IDESP é um indicador que avalia a qualidade da escola, sendo composto por dois critérios: o desempenho dos alunos nos exames de proficiência do SARESP (o quanto aprenderam) e o fluxo escolar (em quanto tempo aprenderam).Assim, nesta avaliação, considera–se que uma boa escola é aquela em que a maior parte dos alunos aprende as competências e habilidades requeridas para a sua série/ano, num período de tempo ideal – o ano letivo.

Para a realização das análises, novamente realizou-se uma limpeza nos dados e selecionou-se o ano de 2018.

No mapas a seguir, pode-se visualizar, inicialmente, que as escolas estaduais no estado de São Paulo, em geral, abordam o Ensino Médio, já as séries iniciais possuem muitos valores vazios por município, ou seja, não possuem avaliação do IDESP pois, provavelmente, são escolas municipais que fornecem esse nível de educação.

Ainda, até 2030, a meta é que as escolas alcancem índice 7 para o ciclo que vai do 1° ao 5° ano, índice 6 para o ciclo que vai do 6° ao 9° ano e índice 5 para o Ensino Médio. Pelas plotagens, a séries iniciais parecem estar mais próximas do objetivo, mesmo que tenham que alcançar notas mais altas.

Com as médias calculadas, observa-se que, realmente, as séries iniciais, pela média, estão mais próximas de alcançar o objetivo. Contudo, existem munícipios que já alcançaram-no, como verificado pelas tabelas abaixo.

Segurança no Estado de São Paulo

Nesse caso, usou-se uma base de dados com informações mensais das ocorrências registradas pela Secretaria de Segurança Publica de São Paulo (SSP). Fez-se a limpeza de dados e acrescentamos uma coluna de ocorrências totais com os dados que supomos relevantes.

Na plotagem do mapa do estado com as ocorrências totais, é evidente o quanto São Paulo se destaca com um número de casos bastante elevado em relação aos outros municípios. Já Torre de Pedra é o município com o menor número de ocorrência, apenas 6.

Contudo, essa soma total de ocorrências pode ser muito influenciada pela quantidade de população do local. Portanto, vale a pena analisar os dados com as proporções. Para isso, fez-se uso de uma base de dados com a quantidade de população por município.

Com as novas visualizações, fica notável o quanto ocorreram mudanças trabalhando com proporções. Neste caso, Torre de Pedra continua sendo o munícipio com menor registro de ocorrências. Contudo, Ilha Comprida, Mongaguá, Jeriquara e Itanhaém possuem proporções mais elevadas do que São Paulo, que se destacava no mapa anterior por disparar com o número de ocorrências em relação aos outros munícipios.

Analíse do fluxo escolar do estado de SP

Essa analíse visa a explorar as diferenças no nível de formação escolar dos municipios de São Paulo. As tabelas contém dados do fluxo escolar agregado a nível de ensino de cada escola. Para quantificar o fluxo, utiliza-se 3 variáveis para cada nível de ensino: a proporção de alunos aprovados, a proporção de alunos reprovados e a proporção de alunos que abandonaram o ano escolar.

Analisando os dados através do novo gráfico podemos ver que o nível de aprovação do ensino médio no estado de São Paulo não é tão baixo e tem uma média de aprovação no ensino médio entre os municípios superior a 77 %.

Podemos observar graças a esse gráfico que a maioria dos estados tem uma aprovação de alunos superior a 50 %, entretanto o que acontece com os alunos que não são aprovados ?

Pela tabela e os dois gráficos acima podemos supor que entre os alunos do ensino médio houve mais alunos que foram reprovados do que alunos que abadoram a escola. Entretanto nem o gráfico e nem a tabela nos permitem de confirrmarr esta hipótese.

Graças a tabela comparativa acima é possível afirmar que na maioria dos municipios com baixa taxa de aprovação, os alunos não completaram o ensino médio devido principalmente a reprovação. Entretanto o que causa essa reprovação?

IDESP e INSE

Agora parte-se para as análises correlacionadas. Será que o Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com as notas do IDESP?

Para respondermos essa pergunta, novamente criou-se uma nova tabela com os dados necessários.

Ao plotar um gráfico de dispersão, esperava que os dados seguissem uma tendência de quanto menor fossem os INSE (já que quanto mais próximo de 0 melhor), maior seriam as notas do IDESP. Contudo, não é o que acontece.

Ao analisar a correlação, ela é negativa (que até concordaria com a ideia de quanto melhor o INSE maior o IDESP), mas muita baixa (cerca de -0,11), portanto, não pode-se concluir que os dados possuem relação.

Segurança no Estado de São Paulo e INSE

Já Índice de nível socioeconômico das escolas (média) por munícipio está relacionado com a Segurança Pública de São Paulo?

Novamente o que temos é um resultado inesperado, afinal, em geral, pensa-se que quanto maior a quantidade de ocorrências, pior seria o INSE, contudo, mesmo havendo uma correlação negativa, ela novamente é muito baixa para afirmar uma relação entre os dados.

Segurança no Estado de São Paulo e Fluxo escolar

Uma das possíveis hipóteses que podemos formular é que o nível de reprovamento e abandono são correlacionados com o total ou com a proporção de crimes nos municípios do estado.

Ao plotar os dois gráficos é possível descobrir uma certa dependencia. O primeiro gráfico indica que a proporção de aprovação no ensino médio e as ocorências criminosas nos Municípios de SP tem uma correlação negativa abaixo de -0.31 para cada crime, isso evidência que há uma tendência de municípios com um total de crime menor a terem um número de aprovação maior.

Conclusões